use RecommendationSys;

go

create procedure P_AdminLogin
@v_userName varchar(14),
@v_userPassword varchar(14)
as
	begin
		if exists (select userID from AdminLogin where userName=@v_userName and userPassword=@v_userPassword)
			select 1
		else
			select 0
	end
go


Create procedure [dbo].[P_UserLogin]
@v_Number varchar(14),
@v_Password varchar(14)
as
	begin
		if exists (select cellphoneNumber from UserLogin where cellphoneNumber=@v_Number and userPassword=@v_Password)
			select 1
		else
			select 0
	end

go


create procedure P_CreateID
@tableName varchar(5),
@time date
as
	--declare @time smalldatetime
	declare @BH varchar(30)
	if @time is null
		set @time=GETDATE()
	begin
		if exists (select codeName from Code where codeName=@tableName)
		begin
			begin tran tr
				update Code set
					@BH=@tableName+CONVERT(CHAR(6),@time,12)
						+RIGHT(POWER(10,nolen)+currentNo+1,nolen),
					currentNo=currentNo+1
				where codeName=@tableName	
			if @@ROWCOUNT<>0
				commit tran tr
		end		
		else
			set @BH='-1'
		select @BH
	end 
	
	
/*create procedure P_CreateID         ---I think this one is better
@tableName varchar(5),
@time date,
@BH varchar(30) output
as
	--declare @time smalldatetime
	--declare @BH varchar(30)
	if @time is null
		set @time=GETDATE()
	begin
		if exists (select codeName from Code where codeName=@tableName)
		begin
			begin tran tr
				update Code set
					@BH=@tableName+CONVERT(CHAR(6),@time,12)
						+RIGHT(POWER(10,nolen)+currentNo+1,nolen),
					currentNo=currentNo+1
				where codeName=@tableName	
			if @@ROWCOUNT<>0
				commit tran tr
			else
			begin
				rollback tran tr
				set @BH='-2'
			end
		end		
		else
			set @BH='-1'
		
	end 
*/

go


create proc P_AddTask
@taskname   varchar(100), 
@sql   varchar(8000),
@dbname   sysname=N'RecommendationSys', 
@freqtype   int=4,
@freqsubtype int=1 ,
@fqinterval   int=1,
@fqsubinterval int=1, 
@time   int=000230 
as
	begin
		declare @jobid varchar(80)
		declare @servername  sysname  
		set @jobid=LEFT(HOST_NAME(),40)+CONVERT(char(19),GETDATE(),120)+CAST(NEWID() as varchar(36))
		if exists (select * from msdb.dbo.sysjobs where name=@taskname)
		exec msdb.dbo.sp_delete_job @job_name=@taskname
		exec msdb.dbo.sp_add_job @job_name=@taskname,@job_id=@jobid
		exec msdb.dbo.sp_add_jobstep @job_id=@jobid,@job_name=@taskname,@step_name=N'transfer data',
									@subsystem=N'TSQL',@database_name=@dbname,@command=@sql
		exec msdb..sp_add_jobschedule @job_id=@jobid,@name=N'run prod',
									  @freq_type=@freqtype,@freq_interval=@fqinterval,
									  @freq_subday_type=@fqsubinterval,@active_start_time=@time
	    set @servername=CONVERT(nvarchar(128),SERVERPROPERTY(N'ServerName'))  
	    exec msdb.dbo.sp_add_jobserver @job_id=@jobid,@server_name= @servername
		
	end
	
go

create proc P_Transferdata
as
begin
	
		declare @id varchar(30)
		declare @amount int 
		declare cur_amount scroll cursor for 
		select count(handsetID), handsetID from TempPopularHandset where clickDate=CONVERT(char(10),GETDATE()-1,20) group by handsetID
		begin tran tr
		open cur_amount
		fetch next from cur_amount into @amount,@id
		while (@@fetch_status=0)
		begin
			update PopularHandset set clickTime=clickTime+@amount,updateTime=GETDATE()
			where handsetID=@id
			if @@ROWCOUNT=0
			begin
				insert PopularHandset select @id,@amount,GETDATE()
			end
			fetch next from cur_amount into @amount,@id
		end
		close cur_amount
		deallocate  cur_amount
		delete from TempPopularHandset where clickDate not between CONVERT(char(10),GETDATE()-1,20) and CONVERT(char(10),GETDATE()-30,20)
		if @@ERROR=0
			commit tran tr
		else
			rollback tran tr
end	

go

create proc P_GetCustomerInfoByMobileNumber
@cellPhoneNumber varchar(10)
as
begin
	
	select c.customerID,c.customerName,c.customerEmail,c.customerGender
	from V_ContractDetail c where c.cellphoneNumber=@cellPhoneNumber
	
end


go

create proc P_GetContractInfoByMobileNumber 
@cellPhoneNumber varchar(10)
as
begin
	
	select c.customerID,c.customerName,c.contractID,c.duration,c.brandName,
	c.handsetModole,c.monthlyFee,em.exMessageAmount,em.exMessagePrice,
	ed.exDataPackage,ed.exDataAmount
	from V_ContractDetail c left join V_ExtraMessage em
	on c.exMessageID=em.exMessageID left join V_ExtraData ed
	on c.exDataID=ed.exDataID
	where c.cellphoneNumber=@cellPhoneNumber
end


go

create proc P_GetCustomerUseHistoryByMobileNumber
@cellPhoneNumber varchar(10)
as
begin
	declare @ID varchar(30)
	select @id=c.contractID
	from V_ContractDetail c
	where c.cellphoneNumber=@cellPhoneNumber
	
	select * from V_CustomerUsageHistroy c where c.contractID=@ID
end
go

go
create proc P_InsertClickRecord
@handsetID varchar(20)
as
begin
	
	insert into TempPopularHandset
    ([handsetID],[clickDate])
	values 
	(@handsetID,getDate())
	
end
